package com.ai.imagetext.repository;

import com.ai.imagetext.entity.GeneratedImage;
import com.ai.imagetext.entity.TextContent;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface GeneratedImageRepository extends JpaRepository<GeneratedImage, Long> {

    /**
     * 根据主键ID查找图片
     */
    @Query(value = "SELECT * FROM generated_images WHERE id = :id AND is_deleted = false", nativeQuery = true)
    Optional<GeneratedImage> findByIdAndIsDeletedFalse(@Param("id") Long id);

    /**
     * 获取数据库最新图片记录
     */
    @Query(value = "SELECT * FROM generated_images ORDER BY created_at DESC LIMIT 1", nativeQuery = true)
    Optional<GeneratedImage> findLatest();

    /**
     * 根据原始图片ID查找生成记录
     */
    List<GeneratedImage> findByOriginalImageIdAndIsDeletedFalse(Long originalImageId);
    
    /**
     * 根据生成图片ID查找记录
     */
    List<GeneratedImage> findByGeneratedImageIdAndIsDeletedFalse(Long generatedImageId);
    
    /**
     * 根据AI生成状态查找记录
     */
    List<GeneratedImage> findByAiGenerationStatusAndIsDeletedFalse(GeneratedImage.AIGenerationStatus status);
    
    /**
     * 根据相似度范围查找记录
     */
    @Query("SELECT gi FROM GeneratedImage gi WHERE gi.isDeleted = false AND " +
           "gi.similarityScore BETWEEN :minScore AND :maxScore")
    List<GeneratedImage> findBySimilarityScoreBetween(@Param("minScore") Double minScore, 
                                                     @Param("maxScore") Double maxScore);
    
    /**
     * 获取最近的生成记录
     */
    Page<GeneratedImage> findByIsDeletedFalseOrderByCreatedAtDesc(Pageable pageable);
    
    /**
     * 根据原始图片ID统计生成数量
     */
    long countByOriginalImageIdAndIsDeletedFalse(Long originalImageId);
    
    /**
     * 统计成功生成的图片数量
     */
    long countByAiGenerationStatusAndIsDeletedFalse(GeneratedImage.AIGenerationStatus status);
    
    /**
     * 软删除生成记录
     */
    @Modifying
    @Query("UPDATE GeneratedImage gi SET gi.isDeleted = true, gi.updatedAt = CURRENT_TIMESTAMP WHERE gi.id = :id")
    void softDeleteById(@Param("id") Long id);
    
    /**
     * 根据原始图片ID软删除所有相关记录
     */
    @Modifying
    @Query("UPDATE GeneratedImage gi SET gi.isDeleted = true, gi.updatedAt = CURRENT_TIMESTAMP " +
           "WHERE gi.originalImage.id = :originalImageId")
    void softDeleteByOriginalImageId(@Param("originalImageId") Long originalImageId);
    
    /**
     * 根据生成图片ID软删除记录
     */
    @Modifying
    @Query("UPDATE GeneratedImage gi SET gi.isDeleted = true, gi.updatedAt = CURRENT_TIMESTAMP " +
           "WHERE gi.generatedImage.id = :generatedImageId")
    void softDeleteByGeneratedImageId(@Param("generatedImageId") Long generatedImageId);
    
    /**
     * 获取生成状态统计
     */
    @Query("SELECT gi.aiGenerationStatus, COUNT(gi) FROM GeneratedImage gi " +
           "WHERE gi.isDeleted = false GROUP BY gi.aiGenerationStatus")
    List<Object[]> getGenerationStatusStatistics();
    
    /**
     * 获取平均相似度评分
     */
    @Query("SELECT AVG(gi.similarityScore) FROM GeneratedImage gi " +
           "WHERE gi.isDeleted = false AND gi.similarityScore IS NOT NULL")
    Double getAverageSimilarityScore();
    
    /**
     * 获取指定原始图片的最高相似度记录
     */
    @Query("SELECT gi FROM GeneratedImage gi WHERE gi.originalImage.id = :originalImageId " +
           "AND gi.isDeleted = false ORDER BY gi.similarityScore DESC")
    Page<GeneratedImage> findTopSimilarByOriginalImageId(@Param("originalImageId") Long originalImageId, 
                                                         Pageable pageable);
}